import os
import xlwt

from common.db import mysqlutil
from config import config


class to_excel():
    def __init__(self, filepath):
        self.filepath = filepath
        self.workbook = xlwt.Workbook(encoding="utf-8")

    def add_sheet_by_list(self, header, datalist, sheetname='Sheet1'):

        sheet1 = self.workbook.add_sheet(sheetname)
        cellindex = 0
        for v in header:
            sheet1.write(0, cellindex, self.__to_str(v))
            cellindex = cellindex + 1

        rowindex = 1
        for rowlist in datalist:
            cellindex = 0
            for v in rowlist:
                sheet1.write(rowindex, cellindex, self.__to_str(v))
                cellindex = cellindex + 1
            rowindex = rowindex + 1
        return self

    def add_sheet_by_dict(self, header, datalist, sheetname='Sheet1', header_showname=None):

        rowlist_lists = []
        for rowdict in datalist:
            rowlist = []
            for k in header:
                rowlist.append(rowdict.get(k, ""))
            rowlist_lists.append(rowlist)

        if header_showname:
            self.add_sheet_by_list(header_showname, rowlist_lists, sheetname)
        else:
            self.add_sheet_by_list(header, rowlist_lists, sheetname)
        return self

    def build(self):
        self.workbook.save(self.filepath)

    def __to_str(self, v):
        str(v)
        return v


def to_html_by_rowlist(header=None, datalist=None):
    ths = ""
    if header and len(header) > 0:
        ths = "<tr>%s</tr>" % (''.join(["<th>%s</th>" % t for t in header]))

    trs = ""
    for rowlist in datalist:
        trs = trs + "<tr>%s</tr>" % (''.join(["<td>%s</td>" % t for t in rowlist]))

    html = """
 <table border='1' bordercolor='#a0c6e5' cellpadding='4' style='border-collapse:collapse;text-align: center;'>
		 %s%s</table>""" % (ths, trs)
    return html


def to_html_by_rowdict(header, datalist, header_showname=None):
    rowlist_lists = []
    for rowdict in datalist:
        rowlist = []
        for k in header:
            rowlist.append(rowdict.get(k, ""))
        rowlist_lists.append(rowlist)

    if header_showname:
        return to_html_by_rowlist(header_showname, rowlist_lists)
    else:
        return to_html_by_rowlist(header, rowlist_lists)


def __testexcel():
    sql = " SELECT userid, useridnet,`ss_盈亏率` from account_money  where abs(`ss_盈亏率`)>0.5  ORDER BY `ss_盈亏率` desc "

    # 根据dict写一个excel
    rs = mysqlutil.execute(sql, mysqlutil.aliyun, dictionary=True)

    # 写一个excel
    to_excel(config.PATH_DESKTOP + os.path.sep + "aaa1.xls") \
        .add_sheet_by_dict(["userid", "useridnet", "ss_盈亏率"], rs, sheetname='主数据').build()
    # 写一个excel并且指定标题名称
    to_excel(config.PATH_DESKTOP + os.path.sep + "aaa2.xls") \
        .add_sheet_by_dict(["userid", "useridnet", "ss_盈亏率"], rs, sheetname='主数据',
                           header_showname=["用户ID", "useridnet", "ss_盈亏率"]).build()
    # 写一个excel, 包含两个sheet
    to_excel(config.PATH_DESKTOP + os.path.sep + "aaa3.xls") \
        .add_sheet_by_dict(["userid", "useridnet", "ss_盈亏率"], rs, sheetname='主数据') \
        .add_sheet_by_dict(["userid", "useridnet", "ss_盈亏率"], rs, sheetname='明细').build()

    # 根据list写一个excel
    rs = mysqlutil.execute(sql, mysqlutil.aliyun)

    to_excel(config.PATH_DESKTOP + os.path.sep + "列表.xls") \
        .add_sheet_by_list(["userid", "useridnet", "ss_盈亏率"], list(rs)).build()


def __testhtml():
    sql = " SELECT userid, useridnet,`ss_盈亏率` from account_money  where abs(`ss_盈亏率`)>0.5  ORDER BY `ss_盈亏率` desc "

    # 根据dict写一个excel
    rs = mysqlutil.execute(sql, mysqlutil.aliyun)
    html = to_html_by_rowlist(["userid", "useridnet", "ss_盈亏率"], rs)
    print(html)
    rs = mysqlutil.execute(sql, mysqlutil.aliyun, dictionary=True)
    html = to_html_by_rowdict(["userid", "useridnet", "ss_盈亏率"], rs)
    print(html)
    html = to_html_by_rowlist(["userid", "useridnet", "ss_盈亏率"], [
        ["张三", "111123412", "1,2"],
        ["李四", "32423512", "3"],
        ["王五", "746354645", "10"],
    ])
    print(html)

    html = to_html_by_rowlist(datalist=[
        ["张三", "111123412", "1,2"],
        ["李四", "32423512", "3"],
        ["王五", "746354645", "10"],
    ])
    print(html)


if __name__ == "__main__":
    __testexcel()
    __testhtml()
